package com.intelligentmeterreadingsys.waterlist.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import com.intelligentmeterreadingsys.BaseDao.BaseDao;
import com.intelligentmeterreadingsys.povo.NavigationPoVo;
import com.intelligentmeterreadingsys.povo.TypePoVo;
import com.intelligentmeterreadingsys.povo.UseWaterInfoPoVo;
import com.intelligentmeterreadingsys.povo.UserWaterVO;
import com.intelligentmeterreadingsys.povo.UserinfoVO;
import com.intelligentmeterreadingsys.povo.WaterInfoVO;
import com.intelligentmeterreadingsys.util.Model;
import com.intelligentmeterreadingsys.util.SqlUtil;

import dbhelper.DbTableName;

/**
 * 表册查询
 * 
 * @author king
 * 
 */
public class WaterListDaoImpl extends BaseDao implements IWaterListDao {

    private SQLiteDatabase db;

    public WaterListDaoImpl(Context context) {
	super(context);
	this.db = super.getDb();

    }

    /**
     * 批量添加水表信息
     */
    @Override
    public boolean insertAllWaterInfo(List<ContentValues> list) {
	boolean b = super.insertALL(list, DbTableName.WATERINFO);
	return b;
    }

    /**
     * 删除所有水表信息
     */
    @Override
    public boolean delectAllWaterInfo() {
	boolean b = super.delectAll(DbTableName.WATERINFO);
	return b;
    }

    /**
     * 查询一个水表信息
     */
    @Override
    public WaterInfoVO queryWaterInfo(WaterInfoVO water) {

	return null;
    }

    /**
     * 抄表时更新数据
     */
    @Override
    public boolean modify(WaterInfoVO water) {
	ContentValues values = new ContentValues();

	values.put("thismonthwater", water.getThismonthwater());
	boolean b = super.modify(water.getId(), DbTableName.WATERINFO, values);

	return b;
    }

    /**
     * 关闭连接
     */
    public void close() {
	if (db != null && db.isOpen()) {
	    db.close();
	}
	super.close();
    }

    /**
     * 查询表册所有用户
     */
    @Override
    public int queryAllUser() {
	int number = 0;
	String sql = "select _id from userinfo where ismultiplecopy='0'";
	Cursor cursor = db.rawQuery(sql, null);

	number = cursor.getCount();

	if (cursor != null) {
	    cursor.close();
	}
	return number;
    }

    /**
     * 查询所有水表
     */
    @Override
    public int queryAllWater() {
	int number = 0;
	String sql = "Select _id From  waterinfo where ismultiplecopy='0'";
	Cursor cursor = db.rawQuery(sql, null);
	number = cursor.getCount();
	if (cursor != null) {
	    cursor.close();
	}
	return number;
    }

    @Override
    public int queryAllNoRedUser() {

	return 0;
    }

    /**
     * 查询没有抄的水表
     */
    @Override
    public int queryAllNoRedWater() {
	int number = 0;
	String sql = "Select _id From  waterinfo where iscopystatus='未抄' or iscopystatus='二次抄表' and ismultiplecopy='0'";
	Cursor cursor = db.rawQuery(sql, null);
	number = cursor.getCount();
	if (cursor != null) {
	    cursor.close();
	}
	return number;
    }

    /**
     * 查询所有报警水表
     */
    @Override
    public int queryAlarmWater() {
	int number = 0;
	String sql = "select _id from waterinfo w where (w.[thismonthwater]-w.[lastmonthwater])<w.[lowalarmlimit] or  (w.[thismonthwater]-w.[lastmonthwater])>w.[alarmlimit]"
		+ "  and iscopystatus='已抄' and ismultiplecopy='0'";
	Cursor cursor = db.rawQuery(sql, null);
	number = cursor.getCount();
	if (cursor != null) {
	    cursor.close();
	}
	return number;
    }

    /**
     * 通过用户编号查询水表信息
     */
    @Override
    public List<WaterInfoVO> queryWaterInfo(String usernumber) {
	List<WaterInfoVO> list = null;
	String sql = "select * from waterinfo where ismultiplecopy='0'";
	if (!TextUtils.isEmpty(usernumber)) {
	    sql = sql + " and usernumber=" + usernumber;
	}
	System.out.println(sql);
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor != null) {
	    list = new ArrayList<WaterInfoVO>();
	} else {
	    return null;
	}
	while (cursor.moveToNext()) {
	    WaterInfoVO water = new WaterInfoVO();
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
	    water.setLastdate(cursor.getString(cursor.getColumnIndex("lastdate")));
	    water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));

	    water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
	    water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
	    list.add(water);

	}
	if (cursor != null) {
	    cursor.close();
	}

	return list;
    }

    /**
     * 通过水表编号查询水表信息
     */
    @Override
    public WaterInfoVO queryWater(String waterNumber) {
	if (TextUtils.isEmpty(waterNumber)) {
	    return null;
	}
	String sql = "select _id,wateradds,latitude,longitude from waterinfo where" + " waternumber=" + waterNumber + " and ismultiplecopy='0'";
	Cursor cursor = db.rawQuery(sql, null);
	WaterInfoVO water = new WaterInfoVO();
	water.setWaternumber(waterNumber);
	if (cursor.moveToNext()) {
	    water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
	    water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
	    water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
	}
	if (cursor != null) {
	    cursor.close();
	}
	return water;

    }

    /**
     * 添加历史用水信息
     * 
     * @param values
     * @return
     */
    public boolean insertUseWater(List<ContentValues> values) {

	return super.insertALL(values, DbTableName.USEWATERINFO);

    }

    /**
     * 查询表册信息
     */
    @Override
    public List<UserWaterVO> queryWaterlist(String username, String usernumber, String waterproperties, String wateradds, String bandingstatus, String copystatus) {
	StringBuffer sb = new StringBuffer();
	sb.append("select w.[_id] as waterid,w.[waternumber],w.[latitude],w.[waterproperties],w.[lastmonthwater],w.[lastdate], ")
		.append("w.[iscopystatus],w.[thismonthwater],w.[longitude],w.[wateradds],u.[username],u.[usernumber]")
		.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='0'");
	sb.append(" and u.[ismultiplecopy]='0'");

	if (username != null && !username.trim().equals("")) {

	    sb.append(" and u.[username]='").append(username).append("'");
	}
	if (usernumber != null && !usernumber.trim().equals("")) {
	    sb.append(" and u.[usernumber]='").append(usernumber).append("'");
	}
	if (waterproperties != null && !waterproperties.trim().equals("")) {
	    sb.append(" and w.[waterproperties]='").append(waterproperties).append("'");
	}
	if (wateradds != null && !wateradds.trim().equals("")) {
	    sb.append(" and w.[wateradds] like'%").append(wateradds).append("%'");
	}
	if (bandingstatus != null && !bandingstatus.trim().equals("")) {
	    if (bandingstatus.equals("2")) {
		sb.append(" and w.[bandingstatus]='").append(bandingstatus).append("'");
	    } else {
		sb.append(" and w.[bandingstatus] in (1,3,4)");
	    }

	}
	if (copystatus != null && !copystatus.trim().equals("")) {
	    sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
	}
	String sql = sb.toString();
	System.out.println(sql);
	List<UserWaterVO> list = new ArrayList<UserWaterVO>();
	Cursor cursor = db.rawQuery(sql, null);
	while (cursor.moveToNext()) {
	    UserWaterVO water = new UserWaterVO();
	    water.setName(cursor.getString(cursor.getColumnIndex("username")));
	    water.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
	    water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
	    water.setLastdate(cursor.getString(cursor.getColumnIndex("lastdate")));
	    String iscopy = cursor.getString(cursor.getColumnIndex("iscopystatus"));
	    if (iscopy.equals("已抄")) {
		water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    } else {
		water.setThismonthwater(0.0);
	    }

	    water.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")));
	    water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
	    water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
	    water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
	    water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
	    list.add(water);

	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;
    }

    /**
     * 查询二两次表数
     */
    @Override
    public int queryTowCopy() {
	String sql = "select _id from waterinfo where iscopystatus='二次抄表' and ismultiplecopy='0'";
	Cursor cursor = db.rawQuery(sql, null);
	int i = cursor.getCount();
	if (cursor != null) {
	    cursor.close();
	}
	return i;
    }

    /**
     * 查询已抄但未上传数
     */
    @Override
    public int queryNoUpload() {
	String sql = "select _id from waterinfo where isupload='0' and iscopystatus='已抄' and ismultiplecopy='0'";
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor == null) {
	    return 0;
	}
	int i = cursor.getCount();
	if (cursor != null) {
	    cursor.close();
	}
	return i;
    }

    /**
     * 查询用户
     */
    @Override
    public List<UserinfoVO> queryUserInfo(String name, String adds, String phone, String usernumber, String usertype) {
	StringBuffer sb = new StringBuffer("select u.[_id],u.[username],u.[useradds],u.[usertype],u.[phone],u.[usernumber],u.[userstatus]");
	sb.append(" from userinfo u where ismultiplecopy='0'");
	if (!TextUtils.isEmpty(name)) {
	    sb.append(" and u.[username]='").append(name).append("'");
	}
	if (!TextUtils.isEmpty(adds)) {
	    sb.append(" and u.[useradds] like '").append(adds).append("'");
	}
	if (!TextUtils.isEmpty(phone)) {
	    sb.append(" and u.[phone]='").append(phone).append("'");
	}
	if (!TextUtils.isEmpty(usernumber)) {
	    sb.append(" and u.[usernumber]='").append(usernumber).append("'");
	}
	if (!TextUtils.isEmpty(usertype) && !usertype.equals("全部")) {

	    sb.append(" and u.[usertype]='").append(usertype).append("'");
	}
	String sql = sb.toString();
	System.out.println(sql);
	Cursor cursor = db.rawQuery(sql, null);
	List<UserinfoVO> list = new ArrayList<UserinfoVO>();
	while (cursor.moveToNext()) {
	    UserinfoVO user = new UserinfoVO();
	    user.setId(cursor.getInt(cursor.getColumnIndex("_id")));
	    user.setName(cursor.getString(cursor.getColumnIndex("username")));
	    user.setAdds(cursor.getString(cursor.getColumnIndex("useradds")));
	    user.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
	    user.setType(cursor.getString(cursor.getColumnIndex("usertype")));
	    user.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
	    user.setStatus(cursor.getString(cursor.getColumnIndex("userstatus")));
	    list.add(user);
	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;
    }

    /**
     * 查询用户类型
     */
    @Override
    public String[] queryUserType() {
	String[] type = null;
	String sql = "select * from type where status=1";

	Cursor cursor = db.rawQuery(sql, null);
	type = new String[cursor.getCount() + 1];
	type[0] = "全部";
	int i = 1;
	while (cursor.moveToNext()) {
	    type[i] = cursor.getString(cursor.getColumnIndex("name"));
	    i++;
	}
	if (cursor != null) {
	    cursor.close();
	}
	return type;
    }

    /**
     * 设置成二次抄表
     */
    @Override
    public boolean Modifytowcopy(String number) {
	ContentValues value = new ContentValues();
	value.put("iscopystatus", "二次抄表");
	int id = db.update(DbTableName.WATERINFO, value, "waternumber=? and ismultiplecopy=?", new String[] { number, "0" });
	if (id > 0) {
	    System.out.println("二次抄表" + number);
	    return true;
	}
	return false;
    }

    @Override
    public boolean delectWaterList() {

	db.delete(DbTableName.USEWATERINFO, "ismultiplecopy=?", new String[] { "0" });
	db.delete(DbTableName.USERINFO, "ismultiplecopy=?", new String[] { "0" });
	super.delectAll(DbTableName.TYPE);
	db.delete(DbTableName.WATERINFO, "ismultiplecopy=?", new String[] { "0" });
	super.delectAll(DbTableName.WATERLIST);
	int id = db.delete(DbTableName.RESOURCES, "status=?", new String[] { "2" });

	if (id > 0) {
	    return true;
	}
	;
	return false;
    }

    /**
     * 通过水表ID查询
     * 
     * @param waterid
     * @return
     */
    public UserWaterVO queryOneWaterlist(String waterid) {
	if (TextUtils.isEmpty(waterid)) {
	    return null;
	}
	StringBuffer sb = new StringBuffer();
	sb.append("select w.[_id] as waterid,w.[adds],w.[waternumber],w.[latitude],w.[waterproperties],w.[lastmonthwater],w.[lastdate], ")
		.append("w.[iscopystatus],w.[thismonthwater],w.[longitude],w.[wateradds],u.[username],u.[usernumber],u.[phone],w.[isupload]")
		.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='0'");
	sb.append(" and u.[ismultiplecopy]='0'");
	sb.append(" and w.[_id]=").append(waterid);
	UserWaterVO water = null;
	String sql = sb.toString();
	System.out.println("one watersql" + sql);
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    water = new UserWaterVO();
	    water.setName(cursor.getString(cursor.getColumnIndex("username")));
	    water.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
	    water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
	    water.setLastdate(cursor.getString(cursor.getColumnIndex("lastdate")));
	    String iscopy = cursor.getString(cursor.getColumnIndex("iscopystatus"));
	    if (iscopy.equals("已抄")) {
		water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    } else {
		water.setThismonthwater(0.0);
	    }

	    water.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")));
	    water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
	    water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
	    water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
	    water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
	    water.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
	    water.setIsupload(cursor.getString(cursor.getColumnIndex("isupload")));
	    water.setLocationadds(cursor.getString(cursor.getColumnIndex("adds")));
	}

	if (cursor != null) {
	    cursor.close();
	}
	return water;

    }

    /**
     * 查询水表历史用水量
     */
    @Override
    public List<UseWaterInfoPoVo> queryUseWater(String waterid, String type) {
	StringBuffer sb = new StringBuffer("select u.[waternumber],u.[month],u.[watercount]  ");
	sb.append("from  usewaterinfo u,waterinfo w where w.[waternumber]=u.[waternumber]");

	sb.append(" and w.[_id]=").append(waterid);
	sb.append(" and w.[ismultiplecopy]='0' and u.[ismultiplecopy]='0'");
	sb.append(" order by u.[month]");
	Cursor cursor = db.rawQuery(sb.toString(), null);
	List<UseWaterInfoPoVo> list = new ArrayList<UseWaterInfoPoVo>();
	String month = "";

	while (cursor.moveToNext()) {
	    String mo = cursor.getString(cursor.getColumnIndex("month"));
	    Double count = Double.parseDouble(cursor.getString(cursor.getColumnIndex("watercount")));

	    if (mo.equals(month)) {
		System.out.println("月份相同");
		UseWaterInfoPoVo use = list.get(list.size() - 1);
		use.setWatercount(use.getWatercount() + count);
		list.remove(list.size() - 1);
		list.add(use);
		month = mo;
	    } else {
		System.out.println("月份不同");
		UseWaterInfoPoVo usewater = new UseWaterInfoPoVo();
		usewater.setMoth(mo);
		usewater.setWatercount(count);

		list.add(usewater);
		month = mo;

	    }

	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;

    }

    /**
     * 分页查询水表信息
     * 
     * @param model
     * @param username
     * @param usernumber
     * @param waterproperties
     * @param wateradds
     * @param bandingstatus
     * @param copystatu
     * @return
     */
    public List<UserWaterVO> queryWaterlist(Model model, String waterlist, String usernumber, String waternumber, String wateradds, String username, String copystatus) {
	StringBuffer sb = new StringBuffer();
	sb.append("select w.[_id] as waterid,w.[waternumber],w.[latitude],w.[waterproperties],w.[lastmonthwater],w.[lastdate], w.[groupingsort],")
		.append("w.[iscopystatus],w.[thismonthwater],w.[longitude],w.[wateradds],u.[username],u.[usernumber],w.[waterlistid],w.[groupwatersort] ")
		.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='0'");
	sb.append(" and u.[ismultiplecopy]='0'");

	if (waterlist != null && !waterlist.trim().equals("") && !waterlist.equals("0")) {

	    sb.append(" and w.[waterlistid]='").append(waterlist).append("'");
	}
	if (usernumber != null && !usernumber.trim().equals("")) {
	    sb.append(" and u.[usernumber]='").append(usernumber).append("'");
	}
	if (username != null && !username.trim().equals("")) {
	    sb.append(" and u.[username] like ' %").append(username).append("%'");
	}
	if (wateradds != null && !wateradds.trim().equals("")) {
	    sb.append(" and w.[wateradds] like'%").append(wateradds).append("%'");
	}
	if (waternumber != null && !waternumber.trim().equals("")) {
	    sb.append(" and w.[waternumber]='").append(waternumber).append("'");
	}
	if (copystatus != null && !copystatus.trim().equals("")) {

	    if (!copystatus.equals("请选择")) {
		sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
	    }
	}
	sb.append(" order by  w.[waterlistid],w.[groupingsort],w.[groupwatersort]");
	String sql = sb.toString();
	System.out.println(sql + "条件查询表册");
	Cursor cursor = SqlUtil.getAllItems(db, (model.getIndex() - 1) * model.getView_Count(), model.getView_Count(), sql);
	List<UserWaterVO> list = new ArrayList<UserWaterVO>();
	while (cursor.moveToNext()) {

	    UserWaterVO water = new UserWaterVO();
	    water.setName(cursor.getString(cursor.getColumnIndex("username")));

	    water.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
	    water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
	    water.setLastdate(cursor.getString(cursor.getColumnIndex("lastdate")));
	    String iscopy = cursor.getString(cursor.getColumnIndex("iscopystatus"));
	    if (iscopy.equals("已抄")) {
		water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    } else {
		water.setThismonthwater(0.0);
	    }

	    water.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")));
	    water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
	    water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
	    water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
	    water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));

	    list.add(water);
	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;

    }

    /**
     * 得到总页数
     * 
     * @param username
     * @param usernumber
     * @param waterproperties
     * @param wateradds
     * @param bandingstatus
     * @param copystatus
     * @return
     */
    public int queryTotalPage(String waterlist, String usernumber, String waternumber, String wateradds, String username, String copystatus) {
	int page = 0;
	StringBuffer sb = new StringBuffer();
	sb.append("select w.[_id] as waterid").append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='0'");
	sb.append(" and u.[ismultiplecopy]='0'");

	if (waterlist != null && !waterlist.trim().equals("") && !waterlist.equals("0")) {

	    sb.append(" and w.[waterlistid]='").append(waterlist).append("'");
	}
	if (usernumber != null && !usernumber.trim().equals("")) {
	    sb.append(" and u.[usernumber]='").append(usernumber).append("'");
	}
	if (waternumber != null && !waternumber.trim().equals("")) {
	    sb.append(" and w.[waternumber]='").append(waternumber).append("'");
	}
	if (wateradds != null && !wateradds.trim().equals("")) {
	    sb.append(" and w.[wateradds] like'%").append(wateradds).append("%'");
	}
	if (username != null && !username.trim().equals("")) {
	    sb.append(" and u.[username] like ' %").append(username).append("%'");
	}
	if (copystatus != null && !copystatus.trim().equals("")) {
	    if (!copystatus.equals("请选择")) {
		sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
	    }

	}
	String sql = sb.toString();
	System.out.println("总页数SQL" + sql);
	int k = super.getCount(sql);
	page = k % 10 > 0 ? k / 10 + 1 : k / 10;
	return page;

    }

    /**
     * 查询所有表册信息
     */
    @Override
    public List<TypePoVo> queryWaterList() {
	List<TypePoVo> list = new ArrayList<TypePoVo>();
	String sql = "select * from " + DbTableName.WATERLIST;
	Cursor cursor = db.rawQuery(sql, null);
	while (cursor.moveToNext()) {
	    TypePoVo type = new TypePoVo();
	    type.setId(cursor.getInt(cursor.getColumnIndex("_id")));

	    type.setName(cursor.getString(cursor.getColumnIndex("name")));

	    type.setNumber(cursor.getString(cursor.getColumnIndex("number")));
	    list.add(type);
	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;
    }

    /**
     * 删除图文导航
     * 
     * @return
     */
    public boolean delectNavigation() {
	boolean b = false;
	int k = db.delete(DbTableName.NAVIGATION, "type=?", new String[] { "1" });
	if (k > 0) {
	    b = true;
	}
	return b;
    }

    /**
     * 查询没有下载的图文数
     * 
     * @return
     */
    public int queryNavigationTotal() {
	int i = 0;
	String sql = "select * from waterinfo where Navigationdownload='0' and ismultiplecopy='0' ";
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    i = cursor.getCount();
	}
	if (cursor != null) {
	    cursor.close();
	}
	return i;
    }

    /**
     * 查询未下载图文导航图片数
     * 
     * @return
     */
    public List<NavigationPoVo> queryNavigtionImgTotal() {

	List<NavigationPoVo> list = new ArrayList<NavigationPoVo>();
	String sql = "SELECT * FROM imgresources where isimg='1' and type='1'";
	System.out.println(sql + "查询未下载的图片");
	Cursor cursor = db.rawQuery(sql, null);
	System.out.println(cursor == null);

	while (cursor.moveToNext()) {

	    NavigationPoVo na = new NavigationPoVo();
	    na.setId(cursor.getString(cursor.getColumnIndex("_id")));
	    na.setAdddesc(cursor.getString(cursor.getColumnIndex("imgpath")));
	    na.setIsimg(cursor.getString(cursor.getColumnIndex("isimg")));
	    na.setIsdown(cursor.getString(cursor.getColumnIndex("isdownload")));
	    na.setOtherid(cursor.getString(cursor.getColumnIndex("othernumber")));
	    System.out.println(na.getOtherid() + "------------------------");
	    na.setStatus(cursor.getString(cursor.getColumnIndex("status")));
	    na.setWebdesc(cursor.getString(cursor.getColumnIndex("imgwebname")));
	    na.setSortnumber(cursor.getInt(cursor.getColumnIndex("Sortnumber")));
	    na.setType(cursor.getString(cursor.getColumnIndex("type")));
	    list.add(na);
	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;
    }

    /**
     * 下载成功后更新图片信息
     * 
     * @param otherid
     * @return
     */
    public boolean modifyNavigation(String otherid, String path) {
	boolean b = false;
	ContentValues values = new ContentValues();
	values.put("imgpath", path);
	values.put("isdownload", "1");
	int k = db.update(DbTableName.NAVIGATION, values, "_id=?", new String[] { otherid });
	if (k > 0) {
	    b = true;
	}
	return b;
    }

    /**
     * 查询未上传的图文数据
     * 
     * @return
     */
    public List<List<NavigationPoVo>> queryNavigationNoUpload() {
	Cursor cursor = null;
	List<List<NavigationPoVo>> lists = new ArrayList<List<NavigationPoVo>>();
	String sql = "select waternumber  from waterinfo where Navigationupload='0' and ismultiplecopy='0'";
	cursor = db.rawQuery(sql, null);
	while (cursor.moveToNext()) {
	    String waternumber = cursor.getString(cursor.getColumnIndex("waternumber"));
	    List<NavigationPoVo> list = new ArrayList<NavigationPoVo>();
	    String sql1 = "select * from imgresources where othernumber='" + waternumber + "' and type='1'  order by Sortnumber";
	    Cursor c = db.rawQuery(sql1, null);
	    while (c.moveToNext()) {
		NavigationPoVo na = new NavigationPoVo();
		na.setAdddesc(c.getString(c.getColumnIndex("imgpath")));
		na.setId(c.getString(c.getColumnIndex("_id")));
		na.setIsimg(c.getString(c.getColumnIndex("isimg")));
		na.setOtherid(waternumber);
		na.setSortnumber(c.getInt(c.getColumnIndex("Sortnumber")));
		na.setType(c.getString(c.getColumnIndex("type")));
		list.add(na);
	    }
	    if (c != null) {
		c.close();
	    }
	    lists.add(list);
	}
	if (cursor != null) {
	    cursor.close();
	}

	return lists;

    }

    /**
     * 查询应该上传的导航数
     * 
     * @return
     */
    public int queryNavigationUpload() {
	int i = 0;
	String sql = "select _id from waterinfo where ismultiplecopy='0' and Navigationupload='0' or Navigationupload='1'";
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    i = cursor.getCount();
	}
	if (cursor != null) {
	    cursor.close();
	}
	return i;
    }

    /**
     * 查看当前是否有表册
     * 
     * @return
     */
    public boolean isWaterList() {
	boolean b = false;

	String sql = "select * from waterlist";
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    b = true;
	}
	if (cursor != null) {
	    cursor.close();
	}
	return b;
    }

    /**
     * 修改水表报警上下限
     * 
     * @return
     */
    public boolean modifyAlarm() {

	// 查询所有水表
	String sql = "select * from waterinfo where ismultiplecopy='0'";

	List<WaterInfoVO> list = new ArrayList<WaterInfoVO>();
	Cursor cursor = db.rawQuery(sql, null);
	while (cursor.moveToNext()) {
	    WaterInfoVO water = new WaterInfoVO();
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setId(cursor.getString(cursor.getColumnIndex("_id")));
	    String sql1 = "select avg( watercount ) as avgwatercount  from usewaterinfo where waternumber='" + water.getWaternumber() + "' and ismultiplecopy='0' ";

	    System.out.println(sql1);
	    Cursor c = db.rawQuery(sql1, null);
	    if (c.moveToNext()) {
		Double d = c.getDouble(c.getColumnIndex("avgwatercount"));
		if (d > 0) {
		    water.setAlarmlimit(d * 1.2);
		    water.setLowalarmlimit(d * 0.8);
		}
	    }
	    if (c != null) {
		c.close();
	    }
	    list.add(water);
	}
	if (cursor != null) {
	    cursor.close();
	}

	Map<String, ContentValues> map = new HashMap<String, ContentValues>();
	for (int i = 0; i < list.size(); i++) {
	    ContentValues values = new ContentValues();
	    values.put("alarmlimit", list.get(i).getAlarmlimit());
	    values.put("lowalarmlimit", list.get(i).getLowalarmlimit());
	    map.put(list.get(i).getId(), values);
	}

	return super.modifyAll(map, DbTableName.WATERINFO);
    }
}
